import xlrd
import pymysql

wb = xlrd.open_workbook(r"C:\Users\Administrator\Documents\WeChat "
                        r"Files\wxid_tho5c27c2wjx22\FileStorage\File\2022-07\5. baidu-员工的人员信息.xls")
ws = wb.sheet_by_index(0)
rows = ws.nrows

data_list = []
for i in range(1, rows):
    data_list.append(ws.row_values(i))

connect = pymysql.connect(host="localhost", user="root", password="", database="百度员工人员信息")

cursor = connect.cursor()
# for i in range(1, rows):
#     sql = "insert into `baidu_information` values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
#     i = i-1
#     序号 = data_list[i][0]
#     工牌号 = data_list[i][1]
#     花名 = data_list[i][2]
#     真实姓名 = data_list[i][3]
#     密码 = data_list[i][4]
#     电话号码 = data_list[i][5]
#     邮箱 = data_list[i][6]
#     年龄 = data_list[i][7]
#     性别 = data_list[i][8]
#     居住地址 = data_list[i][9]
#     入职日期 = data_list[i][10]
#     薪资 = data_list[i][11]
#     职责 = data_list[i][12]
#     外包公司 = data_list[i][13]
#     values = (序号, 工牌号, 花名, 真实姓名, 密码, 电话号码, 邮箱, 年龄, 性别, 居住地址, 入职日期, 薪资, 职责, 外包公司)
#     cursor.execute(sql, values)
#     print(data_list[i][1])
count = "SELECT COUNT(*) FROM baidu_information;"
cursor.execute(count)
a = cursor.fetchone()[0]
print("表格中有", a, "人")

# move = [134, 135, 136, 137, 138, 139, 147, 150, 151, 152, 157, 158, 159, 178, 182, 183, 184, 187, 188, 1703, 1705, 1706]
# link = [130, 131, 132, 145, 155, 156, 175, 176, 185, 186, 1705, 1707, 1708, 1709, 171]
# telecom = [133, 149, 143, 173, 177, 180, 181, 189, 1700, 1701, 1702]
move = "SELECT count(*) from baidu_information where 电话号码 like '134%'or 电话号码 like'135%' or 电话号码 like'136%' or 电话号码 " \
       "like'137%' or 电话号码 like'138%' or 电话号码 like'139%' or 电话号码 like'147%' or 电话号码 like'150%' or 电话号码 like'151%' or " \
       "电话号码 like'152%'or 电话号码 like'157%'or 电话号码 like'158%'or 电话号码 like'159%'or 电话号码 like'178%'or 电话号码 like'182%'or " \
       "电话号码 like'183%'or 电话号码 like'184%'or 电话号码 like'187%'or 电话号码 like'188%'or 电话号码 like'1703%'or 电话号码 like'1705%'or " \
       "电话号码 like'1706%' "
cursor.execute(move)
b1 = cursor.fetchone()[0]
link = "SELECT count(*) from baidu_information where 电话号码 like '130%'or 电话号码 like'131%' or 电话号码 like'132%' or 电话号码 " \
       "like'145%' or 电话号码 like'155%' or 电话号码 like'156%' or 电话号码 like'175%' or 电话号码 like'176%' or 电话号码 like'185%' or " \
       "电话号码 like'186%'or 电话号码 like'1704%'or 电话号码 like'1707%'or 电话号码 like'1708%'or 电话号码 like'1709%'or 电话号码 like'171%'"
cursor.execute(link)
b2 = cursor.fetchone()[0]
telecom = "SELECT count(*) from baidu_information where 电话号码 like '133%'or 电话号码 like'149%' or 电话号码 like'153%' or 电话号码 " \
       "like'173%' or 电话号码 like'177%' or 电话号码 like'180%' or 电话号码 like'181%' or 电话号码 like'189%' or 电话号码 like'1700%' or " \
       "电话号码 like'1701%'or 电话号码 like'1702%'"
cursor.execute(telecom)
b3 = cursor.fetchone()[0]

print("选择移动网络的用户有：", b1, "人,占总人数的比例为", b1/a)
print("选择联通网络的用户有：", b2, "人,占总人数的比例为", b2/a)
print("选择电信网络的用户有：", b3, "人,占总人数的比例为", b3/a)

people1 = "SELECT count(*) from baidu_information where 性别 like '男'"
cursor.execute(people1)
c1 = cursor.fetchone()[0]
print("总公司的男生有：", c1, "人")
people2 = "SELECT count(*) from baidu_information where 性别 like '女'"
cursor.execute(people2)
c2 = cursor.fetchone()[0]
print("总公司的女生有：", c2, "人")

people3 = "SELECT count(*) from baidu_information where 年龄 >45"
cursor.execute(people3)
d = cursor.fetchone()[0]
print("年龄超过45岁的老员工人数有：", d, "人")

people4 = "SELECT count(*) from baidu_information where 薪资 >8000 or 薪资 <3000"
cursor.execute(people4)
e = cursor.fetchone()[0]
print("薪资高于8000元的高薪人员数量和薪资低于3000的底薪人员有：", e, "人")

people5 = "SELECT count(*) from baidu_information where 外包公司 like '%传媒有限公司'"
cursor.execute(people5)
f = cursor.fetchone()[0]
print("去传媒公司的工作的人员有：", f, "人")

people6 = "SELECT count(*) from baidu_information where 居住地址 like '%黑龙江%' or 居住地址 like '%北京%' or 居住地址 like '%福建%' or " \
          "居住地址 like '%四川%' "
cursor.execute(people6)
g = cursor.fetchone()[0]
print("疫情高危地区的人数有：", g, "人")

connect.commit()
print("上传成功！")
cursor.close()
connect.close()
